package dao;




import web.Question;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

public class Course {
	public int cid;
	public String cname;
	public String imagePath;
	public String introduction;
	public String tid;
	private static Connection connection=DBConnection.getDBCourseConnection();
	public static String getNameById(int cid){
		String sql="select cname from course where cid=?";
		PreparedStatement preparedStatement= null;
		String cname=null;
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1,cid);
			ResultSet resultSet=preparedStatement.executeQuery();
			if(resultSet.next()){
				cname=resultSet.getString("cname");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return cname;
	}

	public static String getName(){
		String sql="select cname from course";
		PreparedStatement preparedStatement= null;
		String cname=null;
		try {
			preparedStatement = connection.prepareStatement(sql);
			ResultSet resultSet=preparedStatement.executeQuery();
			if(resultSet.next()){
				cname=resultSet.getString("cname");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return cname;
	}

	public static String getImagePathById(int cid){
		String sql="select imagePath from course where cid=?";
		PreparedStatement preparedStatement= null;
		String value=null;
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1,cid);
			ResultSet resultSet=preparedStatement.executeQuery();
			if(resultSet.next()){
				value=resultSet.getString("imagePath");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return value;
	}

	public static String getImagePath(){
		String sql="select imagePath from course";
		PreparedStatement preparedStatement= null;
		String value=null;
		try {
			preparedStatement = connection.prepareStatement(sql);
			ResultSet resultSet=preparedStatement.executeQuery();
			if(resultSet.next()){
				value=resultSet.getString("imagePath");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return value;
	}


	public static String getTidById(int cid){
	String sql="select tid from course where cid=?";
	PreparedStatement preparedStatement= null;
	String tid=null;
	try {
		preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setInt(1,cid);
		ResultSet resultSet=preparedStatement.executeQuery();
		if(resultSet.next()){
			tid=resultSet.getString("tid");
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return tid;
	}

	public static String getintroById(int cid){
	String sql="select introduction from course where cid=?";
	PreparedStatement preparedStatement= null;
	String introduction=null;
	try {
		preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setInt(1,cid);
		ResultSet resultSet=preparedStatement.executeQuery();
		if(resultSet.next()){
			introduction=resultSet.getString(1);
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return introduction;
	}

    public static List<Course> getCoursesBySid(String id) {
		String sql="select course.cid,cname,imagePath from sc,course where sc.cid=course.cid and sc.sid=?";
		PreparedStatement preparedStatement=null;
		List<Course> courses=new ArrayList<>();
		try {
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setString(1,id);
			ResultSet resultSet=preparedStatement.executeQuery();
			while (resultSet.next()){
				Course course=new Course();
				course.cid=resultSet.getInt(1);
				course.cname=resultSet.getString(2);
				course.imagePath=resultSet.getString(3);
				courses.add(course);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return courses;
    }

    public boolean hasCid(String cid){
	String sql="select cid from course where cid=?";
	PreparedStatement preparedStatement= null;
	boolean result=false;
	try {
		preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setString(1,cid);
		ResultSet resultSet=preparedStatement.executeQuery();
		if(resultSet.next()){
			result=true;
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return result;
	}

	public static boolean insert(String cname,String introduction,String imagePath,String tid){
		String sql="insert into course values(?,?,?,?)";
		PreparedStatement preparedStatement= null;
		boolean result=false;
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1,cname);
			preparedStatement.setString(2,introduction);
			preparedStatement.setString(3,imagePath);
			preparedStatement.setString(4,tid);
			int i=preparedStatement.executeUpdate();
			if(i==1){
				result=true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public static int getCountByTid(String tid){
		String sql="select count(*) from course where tid=?";
		PreparedStatement preparedStatement=null;
		int count=0;
		try{
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1,tid);
			ResultSet resultSet=preparedStatement.executeQuery();
			if(resultSet.next()){
				count=resultSet.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	public static Vector<Integer> getCidsByTid(String tid){
		String sql="select cid from course where tid=?";
		PreparedStatement preparedStatement=null;
		Vector<Integer> cids=new Vector<Integer>();
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1,tid);
			ResultSet resultSet=preparedStatement.executeQuery();
			while (resultSet.next()){
				cids.add(resultSet.getInt(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return cids;
	}

	public static Vector<String> getImagePathsByTid(String tid){
		String sql="select imagePath from course where tid=?";
		PreparedStatement preparedStatement=null;
		Vector<String> paths=new Vector<>();
		try {
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setString(1,tid);
			ResultSet resultSet=preparedStatement.executeQuery();
			while(resultSet.next()){
				paths.add(resultSet.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return paths;
	}

	public static Vector<String> getCnamesByTid(String tid){
		String sql="select cname from course where tid=?";
		PreparedStatement preparedStatement=null;
		Vector<String> cnames=new Vector<>();
		try {
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setString(1,tid);
			ResultSet resultSet=preparedStatement.executeQuery();
			while (resultSet.next()){
				cnames.add(resultSet.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return cnames;
	}

	public static int insertInfo(String cname,String introduction,String tid){
		int result=0;
		try {
			CallableStatement addCourse=connection.prepareCall("{call add_course(?,?,?,?)}");
			addCourse.setString(1,cname);
			addCourse.setString(2,introduction);
			addCourse.setString(3,tid);
			addCourse.registerOutParameter(4, Types.INTEGER);
			addCourse.execute();
			result=addCourse.getInt(4);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public static List<Course> getCoursesByTid(String tid){
		String sql="select * from course where tid=?";
		PreparedStatement preparedStatement=null;
		List<Course> courses=new ArrayList<>();
		try {
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setString(1,tid);
			ResultSet resultSet=preparedStatement.executeQuery();
			while (resultSet.next()){
				Course course=new Course();
				course.cid=resultSet.getInt(1);
				course.cname=resultSet.getString(2);
				course.introduction=resultSet.getString(3);
				course.imagePath=resultSet.getString(4);
				course.tid=resultSet.getString(5);
				courses.add(course);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return courses;
	}

	public static List<Course> getCoursesByNameLike(String wd){
		String sql="select * from course where cname like '%"+wd+"%'";
		PreparedStatement preparedStatement=null;
		List<Course> courses=new ArrayList<>();
		try {
			preparedStatement=connection.prepareStatement(sql);
			ResultSet resultSet=preparedStatement.executeQuery();
			while (resultSet.next()){
				Course course=new Course();
				course.cid=resultSet.getInt(1);
				course.cname=resultSet.getString(2);
				course.introduction=resultSet.getString(3);
				course.imagePath=resultSet.getString(4);
				course.tid=resultSet.getString(5);
				courses.add(course);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return courses;
	}

}
